﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace CutoffDateTester
{
    class Program
    {
        static void Main(string[] args)
        {

            //DateTime dueDatetime = DateTime.ParseExact("2015-07-24 10:30:00", "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
            //DateTime mockNow = DateTime.ParseExact("2015-07-24 10:31:00", "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);

            //if (mockNow >= dueDatetime)
            //{   // Passed due date

            //    // Do work: Send out reminder
            //    dueDatetime = GetNextReminderDate(dueDatetime, "10:30:00");
            //}

            //mockNow = DateTime.ParseExact("2015-07-28 10:31:00", "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
            //dueDatetime = DateTime.ParseExact("2015-07-28 10:30:00", "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
            //if (mockNow >= dueDatetime)
            //{   // Passed due date
            //    // Do work: Send out reminder
            //    dueDatetime = GetNextReminderDate(dueDatetime, "10:30:00");
            //}

            //mockNow = DateTime.ParseExact("2015-08-03 10:31:00", "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
            //dueDatetime = DateTime.ParseExact("2015-08-03 10:30:00", "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
            //if (mockNow >= dueDatetime)
            //{   // Passed due date
            //    // Do work: Send out reminder
            //    dueDatetime = GetNextReminderDate(dueDatetime, "10:30:00");
            //}

            //mockNow = DateTime.ParseExact("2015-08-04 10:31:00", "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
            //dueDatetime = DateTime.ParseExact("2015-08-04 10:30:00", "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
            //if (mockNow >= dueDatetime)
            //{   // Passed due date
            //    // Do work: Send out reminder
            //    dueDatetime = GetNextReminderDate(dueDatetime, "10:30:00");
            //}

            //DateTime testDate = DateTime.ParseExact("2015-08-03 10:31:00", "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
            //bool res = Is7DaysBeforeCutoffDate(testDate);

            
        }

        public static bool Is7DaysBeforeCutoffDate(DateTime targetDate)
        {
            bool result = false;

            string connectionString = @"Data Source=KLDB3\SQL2008;Initial Catalog=CDMS_UAT;Persist Security Info=True;User ID=CDMS_UAT_user;Password=Xh57887266;MultipleActiveResultSets=True;Application Name=Staff-Overtime;Workstation ID=AN2594";

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"SELECT TOP 1 DATEADD([DAY], -7, EndDate) AS 'NotificationDate',* 
FROM    ReportingPeriod 
WHERE   Locked = 1
OR
(LockedDate IS NULL AND NOT EXISTS (SELECT * FROM ReportingPeriod WHERE Locked = 1))
ORDER BY EndDate";

                 DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    DateTime dailyReminderStartDate = ds.Tables[0].Rows[0].Field<DateTime>("NotificationDate");
                    DateTime dailyReminderEndDate = ds.Tables[0].Rows[0].Field<DateTime>("EndDate");

                    if ((targetDate >= dailyReminderStartDate) && (targetDate < dailyReminderEndDate))
                    {
                        result = true;
                    }

                }

            }

            return result;
        }

        public static DateTime GetNextReminderDate(DateTime dueDate, string dueTime)
        {
            DateTime nextReminder = dueDate.AddDays(7);

            string connectionString = @"Data Source=KLDB3\SQL2008;Initial Catalog=CDMS_UAT;Persist Security Info=True;User ID=CDMS_UAT_user;Password=Xh57887266;MultipleActiveResultSets=True;Application Name=Staff-Overtime;Workstation ID=AN2594";

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"SELECT TOP 1 DATEADD([DAY], -7, EndDate) AS 'NotificationDate',* 
FROM    ReportingPeriod 
WHERE   Locked = 1
        OR
        (LockedDate IS NULL AND NOT EXISTS (SELECT * FROM ReportingPeriod WHERE Locked = 1))
ORDER BY EndDate";

                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    DateTime dailyReminderStartDate = ds.Tables[0].Rows[0].Field<DateTime>("NotificationDate");
                    DateTime dailyReminderEndDate = ds.Tables[0].Rows[0].Field<DateTime>("EndDate");
                    //if ((DateTime.Now >= dailyReminderStartDate) && (DateTime.Now < dailyReminderEndDate))
                    //{
                    //    nextReminder = dueDate.AddDays(1);
                    //}
                    if (dailyReminderStartDate <= nextReminder)
                    {
                        string str_dueDateTime = string.Format("{0} {1}", dailyReminderStartDate.ToString("yyyy-MM-dd"), dueTime);
                        //nextReminder = DateTime.ParseExact(str_dueDateTime, "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);

                        DateTime mockToday = DateTime.ParseExact("2015-08-03 10:31:00", "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
                        if ((mockToday >= dailyReminderStartDate) && (mockToday < dailyReminderEndDate))
                            nextReminder = dueDate.AddDays(1);
                        //if (DateTime.Today.AddDays(1) <= nex tReminder)

                    }
                }
                
            }  // using (SqlConnection conn = new SqlConnection(connectionString))

            return nextReminder;
        } 
    }
}
